Skip to Main Content
  • Questions
  • Global non partitioned index on table partitions

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Chris Saxon

Thanks for the question, Saivishnu.

Asked: October 30, 2019 - 7:33 pm UTC

Answered by: Chris Saxon - Last updated: November 01, 2019 - 10:05 am UTC

Category: SQL - Version: 11g

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: On Partitions, Lookups, and Integrity

You Asked

Hi,
I have recently got some sql statements that is not performing well.
select * from v where a=? and b not in(,,,,....) and c =? and rownum<-100 
where v is a view.

Original sql statement is similar to above statement. From explain plan, it seems that a table partition(used to create view v) is not using the index. The index is GLOBAL NON PARTITIONED.
The partition (used by v) got only half a million record but the total records in that table is at least 8 million.
When i directly queried that partitioned table(table as a whole and not just a partition), the index is used but when i performed same on the partition, full table scan is being performed.
According to an article, a full table scan is likely to occur if we are selecting around 20 % of rows in a table.
So, i have come to the conclusion that since the result set will be around 20 % of the records in that partition, it is performing full table scan instead of using INDEX.
But, when a local partitioned index is created, for some reason ORACLE optimizer is using LOCAL partitioned index and not performing full table scan.
Can you please tell me why the optimizer didnt use global non partitioned index when a sql statement was run against a partition but used local partitioned index when executed same query on same partition?


with LiveSQL Test Case:

and we said...

With a global non-partitioned index, the database first searches the index for entries matching your search. Then filters these to just those in the partition you're interested in.

The key point here is there could be many entries in other partitions that satisfy your where clause.

Using your example:

CREATE TABLE TEST (
  pk number,  
  MF VARCHAR2(10),  
  COLOR VARCHAR2(10),  
  WEIGHT NUMBER
)  PARTITION BY LIST(MF) ( 
  PARTITION MF_A VALUES('A'),  
  PARTITION MF_B VALUES('B')  
);

INSERT INTO TEST  
VALUES(8,'A','Y',221);

INSERT INTO TEST  
VALUES(9,'A','Y',221);

INSERT INTO TEST  
VALUES(10,'A','Y',221);

INSERT INTO TEST  
VALUES(11,'B','Y',221);

INSERT INTO TEST  
VALUES(12,'B','Y',221);

commit;
exec dbms_stats.gather_table_stats ( user, 'test' ) ;

set serveroutput off
alter session set statistics_level = all;

CREATE INDEX IDX_TEST ON TEST(pk);

SELECT * FROM TEST WHERE MF='A' AND pk>0;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST +PARTITION'));

----------------------------------------------------------------------------------------------------------    
| Id  | Operation                                  | Name     | Starts | E-Rows | Pstart| Pstop | A-Rows |    
----------------------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT                           |          |      1 |        |       |       |      3 |    
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TEST     |      1 |      3 |     1 |     1 |      3 |    
|*  2 |   INDEX RANGE SCAN                         | IDX_TEST |      1 |      5 |       |       |      5 |    
----------------------------------------------------------------------------------------------------------    
                                                                                                              
Predicate Information (identified by operation id):                                                           
---------------------------------------------------                                                           
                                                                                                              
   1 - filter("MF"='A')                                                                                       
   2 - access("PK">0)


Notice how the plan reads 5 rows from the index (A-rows for line 2). Then filters this down to the three in the relevant partition at line 1.

With a local index, the database can read ONLY those entries in the partition you're accessing.

Continuing the example:

DROP INDEX IDX_TEST;

CREATE INDEX IDX_TEST ON TEST(pk) LOCAL;

SELECT * FROM TEST WHERE MF='A' AND pk>0;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST +PARTITION'));

----------------------------------------------------------------------------------------------------------    
| Id  | Operation                                  | Name     | Starts | E-Rows | Pstart| Pstop | A-Rows |    
----------------------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT                           |          |      1 |        |       |       |      3 |    
|   1 |  PARTITION LIST SINGLE                     |          |      1 |      3 |     1 |     1 |      3 |    
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TEST     |      1 |      3 |     1 |     1 |      3 |    
|*  3 |    INDEX RANGE SCAN                        | IDX_TEST |      1 |      3 |     1 |     1 |      3 |    
----------------------------------------------------------------------------------------------------------    
                                                                                                              
Predicate Information (identified by operation id):                                                           
---------------------------------------------------                                                           
                                                                                                              
   3 - access("PK">0) 


See how the plan only ever reads three rows? It's already found exactly the rows it needs. No need for further filtering.

So with a global index, the database is (potentially) reading entries for every partition. Then throwing all but those for the partition you want away. At some point this is more work than reading all the rows in one partition and discarding the ones you don't need.

In your real query the optimizer has decided you've reached that point where a single partition full scan is cheaper than using the global index. But with a local index, it can narrow down its search to just one partition. Which it's decided is cheaper than a full scan.

For more on how the optimizer decides on using an index vs. full table scan, watch this video series I created:

https://www.youtube.com/watch?v=Rlgb7LwOiHk&list=PL78V83xV2fYlLA-bjMU2ZvUKQOZNrqLEa&index=3

and you rated our response

  (1 rating)

Reviews

November 08, 2019 - 6:03 am UTC

Reviewer: A reader


More to Explore

Performance

Get all the information about database performance in the Database Performance guide.