Home » Server Options » Text & interMedia » Oracle Query with AND clause (10.2.0.4)
Oracle Query with AND clause [message #490214] Fri, 21 January 2011 20:07 Go to next message
lkngstr82is
Messages: 33
Registered: January 2010
Location: USA
Member
We are implementing a complex query with WHERE clause containing a domain indexed column and normal indexes.


SELECT              /*+ index(b BS_NM_DATA_IX1) index(a BS_ORGNL_SBMSN_IX3) */
      a  .seq_nb,
         a.case_loc_cd || a.case_seq_tx || a.case_tier_dt AS case_no,
         a.ISSUE_SYM_ID,
         a.TAX1_ID,
         a.TAX2_ID,
         a.SHRT_NM,
         a.NM_ADRS1_TX,
         a.NM_ADRS2_TX,
         a.NM_ADRS3_TX,
         a.NM_ADRS4_TX,
         a.NM_ADRS5_TX,
         a.NM_ADRS6_TX,
         a.BRNCH_TX,
         a.SBMTG_MP_NB,
         a.ACCNT_ID,
         a.BUY_SLL_CD,
         a.TRD_DT,
         a.TRD_QT
  FROM   bs_orgnl_sbmsn a, bs_nm_data b, bs_seq_data c
 WHERE   catsearch (B.NM_ADRS_TX, '"JAMES KING " | "JAMES KING "', NULL) > 0
         AND B.NAME_ID = C.NAME_ID
         AND C.SEQ_NB = a.seq_nb



This query takes 1 hr. to fetch results. Explain plan shows query is doing a full table scan on BS_SEQ_DATA (673,000,000 rows)-not utilizing existing index (SEQ_NB).

Here is the explain plan
SELECT STATEMENT CHOOSE Cost: 8,231,947 
10 HASH JOIN Cost: 8,231,947 Bytes: 195,336,085,490 
    Cardinality: 675,903,410 
2 TABLE ACCESS BY INDEX ROWID TABLE MKT_TRACK.BS_NM_DATA 
  Cost: 2 Bytes: 57,673,749 Cardinality: 539,007 
1 DOMAIN INDEX INDEX (DOMAIN) MKT_TRACK.BS_NM_DATA_IX1 
9 MERGE JOIN Cost: 2,866,486 Bytes: 123,014,511,984   
   Cardinality: 675,903,912 
 6 SORT JOIN Cost: 906 Bytes: 111,858,748,744 
   Cardinality: 673,847,884 
 5 PARTITION RANGE ALL Cost: 906 Bytes: 111,858,748,744 
   Cardinality: 673,847,884 Partition #: 6 
   Partitions accessed  #1 - #79
 4 TABLE ACCESS BY LOCAL INDEX ROWID TABLE 
    MKT_TRACK.BS_ORGNL_SBMSN Cost: 906 Bytes: 111,858,748,744  
    Cardinality: 673,847,884 Partition #: 6 
    Partitions accessed #1 - #79
 3 INDEX FULL SCAN INDEX MKT_TRACK.INDX_BS_ORGNL_SBMSN_6 
   Cost: 104 Cardinality: 673,847,884 Partition #: 6 
   Partitions accessed #1 - #79
 8 SORT JOIN Cost: 2,865,552 Bytes: 10,773,785,296 
   Cardinality: 673,361,581 
 7 TABLE ACCESS FULL TABLE MKT_TRACK.BS_SEQ_DATA 
   Cost: 514,180 Bytes: 10,773,785,296 Cardinality: 673,361,581 

Re: Oracle Query with AND clause [message #490216 is a reply to message #490214] Fri, 21 January 2011 22:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
If you create a materialized view, then create your ctxcat index on the materialized view, then query the materialized view, then you can get your results using only one domain index. I have provided a simplified demonstration below.

SCOTT@orcl_11gR2> -- tables:
SCOTT@orcl_11gR2> CREATE TABLE bs_orgnl_sbmsn
  2    (seq_nb	    NUMBER)
  3  /

Table created.

SCOTT@orcl_11gR2> CREATE TABLE bs_nm_data
  2    (name_id     NUMBER,
  3  	nm_adrs_tx  VARCHAR2(30))
  4  /

Table created.

SCOTT@orcl_11gR2> CREATE TABLE bs_seq_data
  2    (seq_nb	    NUMBER,
  3  	name_id     NUMBER)
  4  /

Table created.

SCOTT@orcl_11gR2> -- regular indexes:
SCOTT@orcl_11gR2> CREATE INDEX indx_bs_orgnl_sbmsn_6
  2  ON bs_orgnl_sbmsn (seq_nb)
  3  /

Index created.

SCOTT@orcl_11gR2> CREATE INDEX bs_nm_data_name_id
  2  ON bs_nm_data (name_id)
  3  /

Index created.

SCOTT@orcl_11gR2> CREATE INDEX bs_seq_data_idx
  2  ON bs_seq_data (seq_nb, name_id)
  3  /

Index created.

SCOTT@orcl_11gR2> -- materialized view:
SCOTT@orcl_11gR2> CREATE MATERIALIZED VIEW LOG ON bs_orgnl_sbmsn WITH ROWID
  2  /

Materialized view log created.

SCOTT@orcl_11gR2> CREATE MATERIALIZED VIEW LOG ON bs_nm_data WITH ROWID
  2  /

Materialized view log created.

SCOTT@orcl_11gR2> CREATE MATERIALIZED VIEW LOG ON bs_seq_data WITH ROWID
  2  /

Materialized view log created.

SCOTT@orcl_11gR2> CREATE MATERIALIZED VIEW test_mview
  2  REFRESH FAST ON COMMIT
  3  WITH ROWID
  4  AS
  5  SELECT a.rowid arid, b.rowid brid, c.rowid crid,
  6  	    a.seq_nb, b.nm_adrs_tx
  7  FROM   bs_orgnl_sbmsn a, bs_nm_data b, bs_seq_data c
  8  WHERE  b.name_id = c.name_id
  9  AND    c.seq_nb  = a.seq_nb
 10  /

Materialized view created.

SCOTT@orcl_11gR2> -- text index:
SCOTT@orcl_11gR2> CREATE INDEX test_mview_idx
  2  ON test_mview (nm_adrs_tx)
  3  INDEXTYPE IS CTXSYS.CTXCAT
  4  /

Index created.

SCOTT@orcl_11gR2> -- data:
SCOTT@orcl_11gR2> INSERT INTO bs_orgnl_sbmsn VALUES (1)
  2  /

1 row created.

SCOTT@orcl_11gR2> INSERT INTO bs_nm_data VALUES (1, 'JAMES KING')
  2  /

1 row created.

SCOTT@orcl_11gR2> INSERT INTO bs_seq_data VALUES (1, 1)
  2  /

1 row created.

SCOTT@orcl_11gR2> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11gR2> -- gather statistics:
SCOTT@orcl_11gR2> BEGIN
  2    DBMS_STATS.GATHER_TABLE_STATS (USER, 'BS_ORGNL_SBMSN');
  3    DBMS_STATS.GATHER_TABLE_STATS (USER, 'BS_SEQ_DATA');
  4    DBMS_STATS.GATHER_TABLE_STATS (USER, 'BS_NM_DATA');
  5    DBMS_STATS.GATHER_TABLE_STATS (USER, 'test_mview');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> -- query:
SCOTT@orcl_11gR2> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11gR2> SELECT seq_nb, nm_adrs_tx
  2  FROM   test_mview
  3  WHERE  CATSEARCH (nm_adrs_tx, 'JAMES KING', NULL) > 0
  4  /

    SEQ_NB NM_ADRS_TX
---------- ------------------------------
         1 JAMES KING

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3623028249

-------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |     1 |    26 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW ACCESS BY INDEX ROWID| TEST_MVIEW     |     1 |    26 |     3   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX                 | TEST_MVIEW_IDX |       |       |            |          |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CTXSYS"."CATSEARCH"("NM_ADRS_TX",'JAMES KING',NULL)>0)

SCOTT@orcl_11gR2>

Re: Oracle Query with AND clause [message #490236 is a reply to message #490216] Sat, 22 January 2011 11:52 Go to previous messageGo to next message
lkngstr82is
Messages: 33
Registered: January 2010
Location: USA
Member
Barbara,

Thanks for the quick response. We were trying to figure out why query is not utilizing the index on SEQ_NB. Optimizer chooses a full table scan over index scan. We tried forcing a hint and it didn't work. Is this a common issue when you use CTXCAT and normal indexes together?


Re: Oracle Query with AND clause [message #490237 is a reply to message #490236] Sat, 22 January 2011 12:13 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The optimizer will choose whatever it thinks will provide the best execution plan, given what it has to work with. If it has current statistics to use to make that decision, then it will usually come up with the best plan available on its own. Sometimes a full table scan is more efficient. Sometimes it can only use one index or another, not both.

Hints will not force the optimizer to do anything. Hints just influence the optimizer to make it slightly more likely that it will choose what you suggest. There are various kinds of hints. Using a hint that suggests that it combine things may be better than using hints that tell it to use specific indexes.

It is common for queries on multiple tables that use either a ctxcat or context index and other indexes and filter conditions to run slowly. The usual solution is to find a way to combine things, so that it only has to use one index. This usually results in drastic improvement. A materialized view is one method of combining things, especially if you are going to select from the table without the text index. If you were using a context index and not selecting from another table, then a procedure with a user_datastore would be a common solution. There is also a new "filter by" clause, but I haven't yet encountered a case where that has been effective.

There are also other things that affect query speed, such as whether your index is fragmented. You should be periodically synchronizing, optimzing, and rebuilding. You might try dropping and recreating your index and see if that helps. However, I suspect you will see the most drastic improvement if you try the method that I posted with the materialized view.

Previous Topic: Privileges to view DR$ tables
Next Topic: CTXSYS ERROR
Goto Forum:
  


Current Time: Thu Mar 28 17:45:10 CDT 2024