[Tip] Virtual Indices - Nosegment

[dr_gori]

This is for those who do SQL tunning:

let's say that you want to check if the creation of a new index will actually be used by the optimizer. Before you create it, you can test whether it will solve your problem through virtual index.

Oracle will create the index in the data dictionary but will not create the index. Nothing will be stored or accessed!

Select all

SQL> set autotrace traceonly explain 
 
SQL> select name from emp where name = 'Alex'; 
 
Execution Plan 
---------------------------------------------------------- 
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=164 Card=1 Bytes=500) 
   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=164 Card=1 Bytes=500) 

Creating the index

Select all

SQL> create index emp_vir_idx1 on emp(name) nosegment; 
 
Index created. 

Let's see if it will use:

Select all

SQL> set autotrace traceonly explain 
 
SQL> select name from emp where name = 'Alex'; 
 
Execution Plan 
---------------------------------------------------------- 
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=164 Card=1 Bytes=500) 
   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=164 Card=1 Bytes=500) 

Hey, why is not Oracle using my new index? Ah, you must first change your session with the hidden parameter "_USE_NOSEGMENT_INDEXES".

Select all

SQL> alter session set "_use_nosegment_indexes" = true; 
 
Session altered. 
 
SQL> select name from emp where name = 'Alex'; 
 
Execution Plan 
---------------------------------------------------------- 
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=500) 
   1    0   INDEX (RANGE SCAN) OF 'EMP_VIR_IDX1' (NON-UNIQUE) (Cost=1 Card=1 Bytes=500) 

[Nites]

Oops,

I'vê been searching about this type of index ("virtual index") and did some tests here on my basis.
And as much as I tested, I came to the conclusion that here at my base is not working.
Quanod perform Query's Explain, the virtual index is displayed and the cost of the machine decreases.
But the runtime gain does not change at all. If I place to run without the virtual index (full access), it takes 3 minutes. With the virtual index, it takes the same 3 minutes.
I took the test and created a normal index without the nosegment option. With this normal index, Query performed very well. Her Explain was using the new index and the time decreased to 15 seconds approximately.

What's wrong ??? Is any bank configuration ???
The bank I'm working on is 10g release 10.2.0.3.0.

Valewssssssss

[ruevers]

I'm going to get it off this subject, but I think his goal would be to see if Oracle uses the index in the execution plan and should not even improve the time ... because after all it does not exist, there are no segments.

Does anyone know about this subject? Today is Friday and I'm too lazy to search.

[Renan Orati]

Hello, people!!! beauty??

I'm wanting to make my college monograph on the theme "SQL Performance Tunning" !!!

I would like to know if someone has material to pass me ... or book to recommend !! Because most of the material I find is English ...

vlw !!!

[dr_gori]

The goal would be to see if Oracle uses the index in the execution plan and should not even improve the time ... because after all it does not exist, there are no segments.

Does anyone know about this subject? Today is Friday and I'm too lazy to search.

That's exactly that! It does not create the index! The index does not exist! It is only created in the Data Dictionary. So we can see if the index will improve a query or not, through the execution plan.

Imagine a table with 100 million lines, and you have to do tuning on it. Being creating indexes there and for 100 million here will end the bank! Probably this kind of thing is not allowed to do at any time. Hence you create the in-segment index and test the creation of indexes and consultations.

After you saw it works, just create it physically!

[ruevers]

I was worried about the weekend with my kick ... I thought I had given a ball out, but as always you and the Oracle we are connected mentally ... rsrsr
The friend who asked about tunning material, I always indicate The video lessons, it's good to start, have the basics and then you can continue alone.
Here on the site they have them, in the tutorial part explains how to acquire.