Home » SQL & PL/SQL » SQL & PL/SQL » FIRST_VALUE under a subselect problem
FIRST_VALUE under a subselect problem [message #247992] Wed, 27 June 2007 14:25 Go to next message
ddor
Messages: 4
Registered: June 2007
Junior Member
Hello everyone Smile

i have a table which acts as a ChangeLog for certain actions
i must create a view that allows me to retreive a single line giving me the value of "VAL" where the "MEMBER" value and "DATE" is the highest.
BUT i also need an option to filter the results so i will only get results from a certain "MEMBER" (no filter, means highest member).

( i dont have DB at the moment so i just wrote this on the fly, hope it works. )

Database is Oracle 10g.

CREATE TABLE TestTable( UIQ  VARCHAR2(16)
                      , MEMBER VARCHAR2(1)
                      , DT    VARCHAR2(8)
                      , VAL    VARCHAR2(5)
                      )
/

INSERT INTO TestTable(UIQ, MEMBER, DT, VAL) VALUES ('0000000000000000','0', '20070431', '3000');
INSERT INTO TestTable(UIQ, MEMBER, DT, VAL) VALUES ('0000000000000000','3', '20070331', '3000');
INSERT INTO TestTable(UIQ, MEMBER, DT, VAL) VALUES ('0000000000000000','2', '20070331', '2000');
INSERT INTO TestTable(UIQ, MEMBER, DT, VAL) VALUES ('0000000000000000','1', '20070331', '1000');
INSERT INTO TestTable(UIQ, MEMBER, DT, VAL) VALUES ('0000000000000000','0', '20070331', '500');
INSERT INTO TestTable(UIQ, MEMBER, DT, VAL) VALUES ('0000000000000000','3', '20070327', '300');
INSERT INTO TestTable(UIQ, MEMBER, DT, VAL) VALUES ('0000000000000000','0', '20070327', '200');



to "solve" this problem i decided to try and use FIRST_VALUE analytic function.

as such :

SELECT * FROM (
   SELECT UIQ,MEMBER,
    FIRST_VALUE(VAL) OVER (ORDER BY MEMBER DESC, DT DESC) AS MyVal
  FROM TestTable
)
 WHERE UIQ = '0000000000000000'
 AND MEMBER = 2


the problem with this is that it does a FULL TABLE scan instead of using the UIQ index..
putting the WHERE clause inside the subquery "fixes" this but since i need this inside a view i cannot do that..
this is somehow related to using the FIRST_VALUE function since removing it allows the optimizer to optimize the query properly (inserting the WHERE clause inside the subq)


any ideas, comments maybe on a diffrent approach at doing this would be greatly appreciated!

thanks

[Updated on: Wed, 27 June 2007 14:53]

Report message to a moderator

Re: FIRST_VALUE under a subselect problem [message #247995 is a reply to message #247992] Wed, 27 June 2007 14:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your query is not valid.

Btw, it is not equivalent to put the where clause inside or outside the inner query.
Do you want the first value regardless the UIQ or considering it?

Regards
Michel
Re: FIRST_VALUE under a subselect problem [message #247996 is a reply to message #247992] Wed, 27 June 2007 14:35 Go to previous messageGo to next message
ddor
Messages: 4
Registered: June 2007
Junior Member
which query? the creation or the select? i dont have DB here to test so i just wrote this out.. sorry Embarassed

about the second question, yes it should consider it.

*edit: i think i missed the "FROM" clause hehe, my bad.. fixed.

[Updated on: Wed, 27 June 2007 14:36]

Report message to a moderator

Re: FIRST_VALUE under a subselect problem [message #248000 is a reply to message #247996] Wed, 27 June 2007 14:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
yes it should consider it.

So you have to partition the analytical function by this column and the global query will use your index (maybe with the help of the UNNEST hint).

Regards
Michel

Re: FIRST_VALUE under a subselect problem [message #248024 is a reply to message #247992] Wed, 27 June 2007 16:00 Go to previous messageGo to next message
ddor
Messages: 4
Registered: June 2007
Junior Member
I tried your advice, doesnt seem to work still a full table scan..
maybe im doing it wrong, ill post my code tomorrow gotta go to bed now Razz
Re: FIRST_VALUE under a subselect problem [message #248052 is a reply to message #248024] Wed, 27 June 2007 21:46 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SELECT uiq, member
,      MAX(val) KEEP (DENSE_RANK LAST ORDER BY dt) AS MyVal
FROM   TestTable
WHERE  UIQ = '0000000000000000'
AND    MEMBER = 2
GROUP BY uiq, member


Ross Leishman
Re: FIRST_VALUE under a subselect problem [message #248055 is a reply to message #248052] Wed, 27 June 2007 22:05 Go to previous messageGo to next message
ddor
Messages: 4
Registered: June 2007
Junior Member
rleishman wrote on Wed, 27 June 2007 21:46
SELECT uiq, member
,      MAX(val) KEEP (DENSE_RANK LAST ORDER BY dt) AS MyVal
FROM   TestTable
WHERE  UIQ = '0000000000000000'
AND    MEMBER = 2
GROUP BY uiq, member


Ross Leishman


i would need to use this as a view.. so the filter (WHERE UIQ,MEMBER) has to come later.
Re: FIRST_VALUE under a subselect problem [message #248082 is a reply to message #248024] Thu, 28 June 2007 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create index t_idx on testtable(uiq);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'TESTTABLE',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autotrace on explain
SQL> SELECT * FROM (
  2     SELECT UIQ,MEMBER,
  3      FIRST_VALUE(VAL) OVER (PARTITION BY UIQ ORDER BY MEMBER DESC, DT DESC) AS MyVal
  4    FROM TestTable
  5  )
  6   WHERE UIQ = '0000000000000000'
  7   AND MEMBER = 2
  8  /
UIQ              M MYVAL
---------------- - -----
0000000000000000 2 3000

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3348132880

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |     7 |   112 |     3  (34)| 00:00:01 |
|*  1 |  VIEW                         |           |     7 |   112 |     3  (34)| 00:00:01 |
|   2 |   WINDOW SORT                 |           |     7 |   224 |     3  (34)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| TESTTABLE |     7 |   224 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | T_IDX     |     7 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER("MEMBER")=2)
   4 - access("UIQ"='0000000000000000')

Regards
Michel
Re: FIRST_VALUE under a subselect problem [message #248150 is a reply to message #248082] Thu, 28 June 2007 03:08 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
And just to clarify Michel's excellent example, it works (uses an index) because of the PARTITION BY clause. Michel mentioned this some posts ago.

WHERE clauses in the outer query can ONLY be pushed into the inline view by the optimizer if they appear in the PARTITION BY clause.

My example should also work in an inline view (or a named view)
SELECT * FROM (
  SELECT uiq, member
  ,      MAX(val) KEEP (DENSE_RANK LAST ORDER BY dt) AS MyVal
  FROM   TestTable
GROUP BY uiq, member
)
WHERE  UIQ = '0000000000000000'
AND    MEMBER = 2

Any simple WHERE clause on a GROUP BY column should be able to be pushed into the view.

Ross Leishman
Re: FIRST_VALUE under a subselect problem [message #248152 is a reply to message #248150] Thu, 28 June 2007 03:12 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To complete Ross' answer:
SQL> SELECT * FROM (
  2    SELECT uiq, member
  3    ,      MAX(val) KEEP (DENSE_RANK LAST ORDER BY dt) AS MyVal
  4    FROM   TestTable
  5  GROUP BY uiq, member
  6  )
  7  WHERE  UIQ = '0000000000000000'
  8  AND    MEMBER = 2
  9  /
UIQ              M MYVAL
---------------- - -----
0000000000000000 2 2000

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2258613832

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     2 |    64 |     3  (34)| 00:00:01 |
|   1 |  SORT GROUP BY               |           |     2 |    64 |     3  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TESTTABLE |     2 |    64 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_IDX     |     7 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - filter(TO_NUMBER("MEMBER")=2)
   3 - access("UIQ"='0000000000000000')

Regards
Michel
Previous Topic: passing table name and column names as parameter to procedure
Next Topic: How can I generate output like Matrix Transposition?
Goto Forum:
  


Current Time: Sun Dec 04 12:57:19 CST 2016

Total time taken to generate the page: 0.07677 seconds