Re: Indexing View Columns?

From: onedbguru <onedbguru_at_yahoo.com>
Date: Thu, 17 Mar 2011 17:45:45 -0700 (PDT)
Message-ID: <938aeb05-c0d8-4081-85fe-e9cf57353c26_at_p16g2000vbo.googlegroups.com>

On Mar 17, 8:41 am, NewUser2008 <mcse..._at_gmail.com> wrote:
> On 15 Mrz., 22:25, joel garry <joel-ga..._at_home.com> wrote:> Well, you can upgrade.  http://blogs.oracle.com/optimizer/2011/02/optimizer_transformations_j...
>
> Ah, thank you very much for your input, however, even 11g will not
> solve my difficulties since they do not result from slow singluar
> queries. All the UNION ALL contents from above are in fact supported
> by appropriate indices; what makes the query slow is that the view
> will always be executed completely; if there are 2000 small, but fast
> queries being executed the whole result becomes slow anyway. So, I'd
> need a way to tell the DBMS only to execute the appropriate UNION ALL
> line(s) which by design is not possible because the required line
> number will first be available _after_ the execution of the statement.
>
> I thought that the explain plan somehow would give a hint on which
> part shall be executed, but apparently it does not. So unless there is
> a way to feed parts of a "WHERE" clause into a function parameter set
> automatically (maybe only via highly specialized triggers) I fear I
> cannot solve my dilemma...
>
> Thanks again, NewUser2k8

 You CAN get there, you just need to figure out exactly what it is you are looking for... If you were to give us a scaled down example of what your real query looks like, you may get better information as we can only guess at what is the real problem you are trying to solve.

GIVEN:
SQL> select * from abc;

        ID NICEDATE A

---------- --------- ----------
      1000 07-MAR-11 ABC
      1001 06-MAR-11 ABD
      1002 05-MAR-11 ABE
      1003 04-MAR-11 ABF
      1004 03-MAR-11 ABG
      1005 02-MAR-11 ABH
      1006 01-MAR-11 ABI
      1007 28-FEB-11 ABJ
      1008 27-FEB-11 ABK
      1009 26-FEB-11 ABL
      1010 25-FEB-11 ABM
      1011 24-FEB-11 ABM
      1012 23-FEB-11 ABN

13 rows selected.

You can use RANK
SQL> select id, rank() over (order by a) rank, nicedate,a from abc;

        ID RANK NICEDATE A

---------- ---------- --------- ----------
      1000          1 07-MAR-11 ABC
      1001          2 06-MAR-11 ABD
      1002          3 05-MAR-11 ABE
      1003          4 04-MAR-11 ABF
      1004          5 03-MAR-11 ABG
      1005          6 02-MAR-11 ABH
      1006          7 01-MAR-11 ABI
      1007          8 28-FEB-11 ABJ
      1008          9 27-FEB-11 ABK
      1009         10 26-FEB-11 ABL
      1010         11 25-FEB-11 ABM
      1011         11 24-FEB-11 ABM
      1012         13 23-FEB-11 ABN

SQL> select id, (rank() over (order by a))+100 rank, nicedate,a from abc;

        ID RANK NICEDATE A

---------- ---------- --------- ----------
      1000        101 07-MAR-11 ABC
      1001        102 06-MAR-11 ABD
      1002        103 05-MAR-11 ABE
      1003        104 04-MAR-11 ABF
      1004        105 03-MAR-11 ABG
      1005        106 02-MAR-11 ABH
      1006        107 01-MAR-11 ABI
      1007        108 28-FEB-11 ABJ
      1008        109 27-FEB-11 ABK
      1009        110 26-FEB-11 ABL
      1010        111 25-FEB-11 ABM
      1011        111 24-FEB-11 ABM
      1012        113 23-FEB-11 ABN

13 rows selected.
OR You can use ROWNUM???

SQL> select id col1,rownum col2,nicedate col3 from  (select id, nicedate from abc where a='ABD'    union all
  select id, nicedate from abc where a='ABC')  /

      COL1    COL2       COL3
     ---------    ----------      ---------
     1001          1          06-MAR-11
     1000          2          07-MAR-11

Received on Thu Mar 17 2011 - 19:45:45 CDT