Solved

NVL Full table

Posted on 2011-10-19
395 Views

``````SELECT A.NIS,A.PER_NOMBRE,A.PER_PRIMER_APELLIDO,A.TIPO_DOCUMENTO,A.NUM_DOC_IDENTIDAD,RGA.PRF_ID,RGA.FIC_ID
COMUN.PERSONA A
WHERE RGA.NIS               = A.NIS
AND A.NIS                 = NVL(:B8, A.NIS)
AND A.PER_NOMBRE          LIKE NVL(:B7, '%')
AND A.PER_PRIMER_APELLIDO LIKE NVL(:B6, '%')
AND A.TIPO_DOCUMENTO      = NVL(:B5, A.TIPO_DOCUMENTO)
AND RGA.PRF_ID            = NVL(:B2, RGA.PRF_ID)
AND RGA.FIC_ID            = NVL(:B1, RGA.FIC_ID)
AND ROWNUM <= 500
``````
0
Question by:jairdur

LVL 76

Expert Comment

Might be an optimizer 'bug'.

Try a hint:

SELECT /*+ FIRST_ROWS */ A.NIS
...

0

LVL 73

Expert Comment

create an index on persona

I'd have to know your data to recommend the most effective index.

but to start with you could try using a composite index on all of the columns in the where clause
0

LVL 73

Expert Comment

ah, on second glance  the NVL is the problem  you're applying a function to a column

0

LVL 76

Expert Comment

To force the used of L_PER_PK, you can force an index hint or remove the Optimizer (not really recommended) with a RULE hint:

SELECT /*+ RULE*/ A.NIS
...
0

Author Comment

slightwv:  is equal thanks

sdstuber: the table have indexes see please
0

Author Comment

wait
0

Author Comment

slightwv: HINTS no applay
0

LVL 76

Expert Comment

>>slightwv:  is equal thanks

Even using RULE based?
0

Author Comment

exact sdstuber

what i do?
0

Author Comment

0

LVL 73

Expert Comment

>>> sdstuber: the table have indexes see please

yes, I know.  sorry.  I should have "create a better index"  not "create an index"

your  use of a function on a.nis  prevents an index on a.nis from being used.

specifically   -    NVL(:b8,a.nis)

for the optimizer
this
AND A.NIS                 = NVL(:B8, A.NIS)
is significantly different than
AND A.NIS                 = 7355815
0

LVL 73

Expert Comment

I shold probably elaborate...

AND A.NIS                 = NVL(:B8, A.NIS)

you might look at this as a query that should use an index because A.NIS is the search criteria.
However,  you ALSO have A.NIS inside the function call.

so,  while you might find it convenient to think of this condition as...

"lookup when there is a value, otherwise don't do anything "

in reality you have  "do function call on column,  check if function call on column is the same as other value, where other value is itself the same column "

0

Author Comment

NULL?
0

Author Comment

AND A.NIS                 = NVL(NULL/*:B8*/, A.NIS)
0

Author Comment

review the query
0

Author Comment

COMUN.PERSONA A
WHERE RGA.NIS               = A.NIS
AND A.NIS                 = NVL(:B8, A.NIS)
AND A.PER_NOMBRE          LIKE NVL(:B7, '%')
AND A.PER_PRIMER_APELLIDO LIKE NVL(:B6, '%')
AND A.TIPO_DOCUMENTO      = NVL(:B5, A.TIPO_DOCUMENTO)
AND RGA.PRF_ID            = NVL(:B2, RGA.PRF_ID)
AND RGA.FIC_ID            = NVL(:B1, RGA.FIC_ID)
AND ROWNUM <= 500
0

Author Comment

the SQL statement has 8 possibilities of making the request or combinations thereof. See?
0

LVL 76

Expert Comment

The issue is in 10g, as soon as the optimizer sees a function, the NVL, on an indexed column, it won't use the index.

We are both working to see if there is a way around this.

There is in 11gR2.
0

Author Comment

0

LVL 73

Accepted Solution

you may want to use dynamic sql to construct your query
to exclude conditions where the parameter is NULL

0

LVL 73

Expert Comment

what indexes do you have on persona?  is the index on NIS the only one?
0

Author Comment

sdstuber: SAY what indexes do you have on persona?  is the index on NIS the only one? NO
``````-- Create table
create table COMUN.PERSONA
(
NIS                  NUMBER(12),
TIPO_DOCUMENTO       VARCHAR2(3),
PER_NOMBRE           VARCHAR2(50),
PER_PRIMER_APELLIDO  VARCHAR2(50),
PER_SEGUNDO_APELLIDO VARCHAR2(50),
PER_CORREO_E         VARCHAR2(50)
)
partition by hash (NIS)
(
partition PER_NIS_P1
tablespace PERSONA_DAT_01,
partition PER_NIS_P2
tablespace PERSONA_DAT_02,
partition PER_NIS_P3
tablespace PERSONA_DAT_01,
partition PER_NIS_P4
tablespace PERSONA_DAT_02
);
-- Create/Recreate primary, unique and foreign key constraints
alter table COMUN.PERSONA  add constraint PER_PK primary key (NIS);
alter table COMUN.PERSONA  add constraint USR_PER_FK foreign key (NIS)
references COMUN.USUARIO (NIS);
-- Create/Recreate check constraints
alter table COMUN.PERSONA  add constraint PER_CORREO_E_NN
check ("PER_CORREO_E" IS NOT NULL);
alter table COMUN.PERSONA  add constraint PER_NOMBRE_NN
check ("PER_NOMBRE" IS NOT NULL);
alter table COMUN.PERSONA  add constraint PER_PRIMER_APELLIDO_NN
check ("PER_PRIMER_APELLIDO" IS NOT NULL);
alter table COMUN.PERSONA  add constraint PER_TIPO_DOCUMENTO_CK
check (TIPO_DOCUMENTO IN ('CC', 'TI', 'CE', 'NIT', 'NCS','NA', 'RC','NIS','RUI','PS','DNI'));
alter table COMUN.PERSONA  add constraint TIPO_DOCUMENTO_PER_NN
check ("TIPO_DOCUMENTO" IS NOT NULL);
alter table COMUN.PERSONA  add constraint USR_PER_NN
check ("NIS" IS NOT NULL);
-- Create/Recreate indexes
create index COMUN.I_PER_NOMBRE on COMUN.PERSONA (PER_NOMBRE);
create unique index COMUN.I_PER_NUM_DOCUMENTO_UK on COMUN.PERSONA (NUM_DOC_IDENTIDAD, TIPO_DOCUMENTO);
create unique index COMUN.I_PER_PK on COMUN.PERSONA (NIS);
create index COMUN.I_PER_PRIMER_APELLIDO on COMUN.PERSONA (PER_PRIMER_APELLIDO);
``````
0

LVL 73

Expert Comment

in that case I recommend going with dynamic sql.
that way your parameters will generate a plan specific to the available indexes that apply to your parameterized conditions
0

LVL 76

Assisted Solution

Not as good as dynamic SQL but...

In my tests this isn't as good as the full index but appears to be slightly better of a plan than the FULL TABLE SCAN.

You might need to add another parameter depending on what is calling this.

...
AND A.NIS                 = DECODE(:B8, null, A.NIS, :B8)
...
0

LVL 3

Expert Comment

Try this

AND (A.NIS  = :B8 or (:B8 is null and 1 = 1))
0

LVL 73

Expert Comment

the optimizer will remove the 1=1

and it will simply become...

AND (A.NIS  = :B8 or :B8 is null)

or more likely  it will do the null evaluation first...

AND (:B8 is null OR A.NIS = :B8)

in any case,  the 1=1 bit isn't helpful.   (test it, you can see for yourself)

there is a trick for doing that to make dynamic sql generation more convienient,  but without context it doesn't do anything

0

LVL 3

Expert Comment

yes optimizer wont evaluate 1 = 1.

:B8 is null - It won't affect the performance.

A.NIS  = :B8 - I am sure optimizer will consider the index.
0

LVL 73

Expert Comment

actually it's likely the index will NOT be used

test it, you'll see  a Filter Predicate  of (:B8 is null OR A.NIS = :B8)

the "OR" will negate the index
0

Author Comment

Serñor slightwv: So far the DECODE has been effective

sdstuber: That might work but with UNION , right?
0

LVL 73

Expert Comment

>>> That might work

what is "that"  ?
0

Author Comment

Could
0

LVL 73

Expert Comment

I have no idea what "could" means ?

what are you asking me to evaluate?  Post some code or a link to a post or a post id or something that indicates what you're referring to
0

Author Comment

This (:B8 is null OR A.NIS = :B8) could be but with  4  UNIONs
0

LVL 73

Expert Comment

yes it could work as far as functionality goes, but it's not going to help with indexes, and, with 4 unions, you're hitting the same data again and again.
0

Author Comment

Sorry 6 UNION

You tried it?
0

LVL 73

Expert Comment

no I did not.  I tested on smaller scale and extrapolated to 4 and 6 (or more)

I could be wrong though.  Also, it will depend somewhat on your particular settings.
If you have more memory, particularly more hash/sort space the optimizer has additional options it can attempt and plans can change quite a bit.

In this particular case,  with the NULL/ NOT NULL check  you're limiting what the optimizer can do on its own.
0

LVL 76

Expert Comment

I would like to point out that you do not have to run the SQL to generate the execution plan.  Then you can actually test a variety of different SQL without actually impacting the system.

Test whatever SQL you think might work for you.

I prefer sqlplus:

explain plan for select sysdate from dual;

Then display it with:
select * from table(dbms_xplan.display);

0

Author Closing Comment

DECODE no full scan
SQL Dynamic is good

Thank Señores
0

Featured Post

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.