Home » SQL & PL/SQL » SQL & PL/SQL » Is indexes are not used if the pl/sql tables are refered in the where clause? (oracle 10g,windows)
Is indexes are not used if the pl/sql tables are refered in the where clause? [message #310552] Tue, 01 April 2008 11:02 Go to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi All,

currently In my package i am using a pl/sql table in the where clause bcz of that the indexes is not properly using.
sample code is as

SELECT *
    FROM   (SELECT A.PLCY_NBR,
                   A.CO_CD,
                   'AW' PARTY_ROLE_CD,
                   B.AGNT_LAST_NAME || ',' || B.AGNT_FIRST_NAME || ',' || 
                   B.AGNT_MIDDLE_INITL_NAME || ',' || '' AS FULL_NAME,
                   ROW_NUMBER() OVER(PARTITION BY A.PLCY_NBR, A.CO_CD, B.CLNT_REF_NBR 
                     ORDER BY B.CLNT_REF_NBR) SEQ
     FROM   CLEAR_DAILY_FACT_VW A,
            AGNT_DIM            B
     WHERE  A.AGNT_DIM_ID = B.AGNT_DIM_ID AND
            (A.PLCY_NBR, A.CO_CD) IN
            (SELECT PLCY_NBR,
                    CO_CD
             FROM   TABLE(CAST(PLCY_NBR_TBL_IN AS HLDNG_SRCH_INFO_OBJ_TBL_TYPE))))
            WHERE  SEQ = 1;

So,I modifed the code and created a global temp table
and used it as follows

SELECT *
FROM (SELECT A.PLCY_NBR,
             A.CO_CD,
             'AW' PARTY_ROLE_CD,
             B.AGNT_LAST_NAME || ',' || B.AGNT_FIRST_NAME || ',' ||
             B.AGNT_MIDDLE_INITL_NAME || ',' || '' AS FULL_NAME,
             ROW_NUMBER() OVER(PARTITION BY A.PLCY_NBR, A.CO_CD, B.CLNT_REF_NBR 
                ORDER BY B.CLNT_REF_NBR) SEQ
      FROM CLEAR_DAILY_FACT_VW A, AGNT_DIM B, HLDNG_SRCH_TBL_GBL C
      WHERE A.AGNT_DIM_ID = B.AGNT_DIM_ID
        AND A.PLCY_NBR = C.PLCY_NBR
        AND A.CO_CD = C.CO_CD)
WHERE SEQ = 1;

Now it is properly using all the indexs.


I have doubt
Quote:
why the indexs are not properly used in the first case?
IF the the pl/sql tables are used in the where clause then the indexes will not be used on the refering columns?

[Updated on: Tue, 01 April 2008 11:12] by Moderator

Report message to a moderator

Re: Is indexes are not used if the pl/sql tables are refered in the where clause? [message #310555 is a reply to message #310552] Tue, 01 April 2008 11:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post execution plan.

Regards
Michel
Re: Is indexes are not used if the pl/sql tables are refered in the where clause? [message #310586 is a reply to message #310555] Tue, 01 April 2008 13:52 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi,

Unfortunately i don't have excution plan as the code is directly changed in the UAT env by our DBA team stating that bcz of the pl/sql table in the where clause the indexes created on PLCY_NBR and CO_CD were not untilized.so,they done the changes by using the Global_temp table.

But,I want know is there any constraint/compulsion that if pl/sql tables are used in the where clause then the referenced columns indexes were not used?

Thanks in advance....
Re: Is indexes are not used if the pl/sql tables are refered in the where clause? [message #310627 is a reply to message #310586] Tue, 01 April 2008 16:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
The optimizer uses statistics, things like how many rows are in the table and the cardinality to determine the best execution plan. However, when you use the TABLE function and CAST, even if you have analyzed the table and gathered statistics, it does not have that information. Typically such queries return a small set of rows, but by default the optimizer assigns a higher value. You can change this with hints, like the cardinality hint, or possibly with things like rownum > 0 or the materialize hint. Please see the demonstration below, first without a hint, then with the cardinality hint and notice the differences in the rows and bytes columns. When you are joining this to another table, it can greatly affect the execution plan selected by the optimizer.

-- test environment:
SCOTT@orcl_11g> create or replace type test_typ as table of varchar2 (255);
  2  /

Type created.

SCOTT@orcl_11g> create table test_tab
  2    (nested_tab     test_typ)
  3    nested table nested_tab store as nested_nt
  4  /

Table created.

SCOTT@orcl_11g> insert into test_tab values (test_typ ('DEPT', 'EMP'))
  2  /

1 row created.

SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'TEST_TAB')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SET AUTOTRACE ON EXPLAIN


-- without hint:
SCOTT@orcl_11g> SELECT t.*
  2  FROM   test_tab,
  3  	    TABLE (CAST (nested_tab AS test_typ)) t
  4  /

COLUMN_VALUE
----------------------------------------------------------------------------------------------------
DEPT
EMP


Execution Plan
----------------------------------------------------------
Plan hash value: 300059441

-------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |  8168 |   151K|    32   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                       |           |  8168 |   151K|    32   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL                 | TEST_TAB  |     1 |    17 |     3   (0)| 00:00:01 |
|   3 |   COLLECTION ITERATOR SUBQUERY FETCH|           |       |       |            |          |
|*  4 |    TABLE ACCESS FULL                | NESTED_NT |     1 |   139 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   4 - filter("NESTED_TABLE_ID"=:B1)

Note
-----
   - dynamic sampling used for this statement


SCOTT@orcl_11g> SELECT /*+ CARDINALITY (t 1) */ t.*
  2  FROM   test_tab,
  3  	    TABLE (CAST (nested_tab AS test_typ)) t
  4  /

COLUMN_VALUE
----------------------------------------------------------------------------------------------------
DEPT
EMP


Execution Plan
----------------------------------------------------------
Plan hash value: 300059441

-------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |     1 |    19 |    32   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                       |           |     1 |    19 |    32   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL                 | TEST_TAB  |     1 |    17 |     3   (0)| 00:00:01 |
|   3 |   COLLECTION ITERATOR SUBQUERY FETCH|           |       |       |            |          |
|*  4 |    TABLE ACCESS FULL                | NESTED_NT |     1 |   139 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   4 - filter("NESTED_TABLE_ID"=:B1)

Note
-----
   - dynamic sampling used for this statement

SCOTT@orcl_11g> 


-- So, in your subquery, try something like:
            (SELECT /*+ CARDINALITY (c 1) */ PLCY_NBR,
                    CO_CD
             FROM   TABLE(CAST(PLCY_NBR_TBL_IN AS HLDNG_SRCH_INFO_OBJ_TBL_TYPE)) c)

Re: Is indexes are not used if the pl/sql tables are refered in the where clause? [message #310681 is a reply to message #310552] Wed, 02 April 2008 01:16 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Thanks for the great demonstration,

That means without hints(like cardinality hints)
if pl/sql tables are used in the where clause then the referenced columns indexes were not used?

Thanks,
Re: Is indexes are not used if the pl/sql tables are refered in the where clause? [message #310706 is a reply to message #310681] Wed, 02 April 2008 02:37 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
No, this means that without hints Oracle will GUESS as to the contents of your collection and construct a plan accordingly.

Depending on how that guess fits in with the rest of the query, it may or may not use an index.

Ross Leishman
Re: Is indexes are not used if the pl/sql tables are refered in the where clause? [message #311118 is a reply to message #310552] Thu, 03 April 2008 07:56 Go to previous message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Thank you all..
Previous Topic: PL/SQL Exception
Next Topic: How to use DBMS_Stats
Goto Forum:
  


Current Time: Thu Dec 08 12:17:47 CST 2016

Total time taken to generate the page: 0.10493 seconds