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  |
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  |
 |
Barbara Boehmer
Messages: 9100
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 |
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 |
|
Barbara Boehmer
Messages: 9100
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.
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 22:17:45 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
