Good Morning Gentleman,

Requirment:
Need to check whether there Is a record satisfying given criteria
(transnum = :Itransnum in a 10 million row table.
(lets assume this records can exist as the first row or last row). All
I want is to find out if this row exists. But my problem is evne if
this is the first row Oracle still continues going through 10 million
records. Am i writing the select correct?

SELECT COUNT(*)
INTO :Icount
FROM archive_tbl
WHERE transnum = :Itransnum;

The above willl go through all the records... which is not optimal. I
want to get out when a single row is found. The following also DOES NOT
do the trick....it scannes the entire rows...

SELECT COUNT(*)
INTO :Icount
FROM archive_tbl
WHERE transnum = :Itransnum and rownum=1;

Any better way of writing this? Your help is very much appreciated.

Cheers
Ivan

Hi Ivan,

OK, I'll be the first to ask the stuipid question. Is it indexed? How
do you know it is reading all the rows?

Regards,

Steve

I would go with PL/SQL.

br,
stephan

Hi Steve,

Sorry for not giving the information on index side. This table is for
heavy insert and for performance reasons we have not created indexes.

Cheers
Ivan

Hi Stephan,

Thank you for your suggestion. But how would youn do it in PL/SQL, are
you referring a row by fetch and comparison?

Cheers
Ivan

"eye1h" <eye1h@yahoo.com> wrote in message
news:1145626367.886473.81090@i40g2000cwc.googlegro ups.com...
> Good Morning Gentleman,
>
> Requirment:
> Need to check whether there Is a record satisfying given criteria
> (transnum = :Itransnum in a 10 million row table.
> (lets assume this records can exist as the first row or last row). All
> I want is to find out if this row exists. But my problem is evne if
> this is the first row Oracle still continues going through 10 million
> records. Am i writing the select correct?
>
> SELECT COUNT(*)
> INTO :Icount
> FROM archive_tbl
> WHERE transnum = :Itransnum;
>
> The above willl go through all the records... which is not optimal. I
> want to get out when a single row is found. The following also DOES NOT
> do the trick....it scannes the entire rows...
>
> SELECT COUNT(*)
> INTO :Icount
> FROM archive_tbl
> WHERE transnum = :Itransnum and rownum=1;
>
> Any better way of writing this? Your help is very much appreciated.
>
> Cheers
> Ivan
>
How about:

SELECT 1
INTO :Icount
FROM archive_tbl
WHERE transnum = :Itransnum and rownum < 2;

--
Terry Dykstra

Hi Terry,

1) This will also go through the full table scan to get rows (transnum
= :Itransnum)
2) Then the results will logically sequenced and results which are less
the rownnum 2 will be displayed.

This has the same problems like the original query.

Cheers
Ivan

Are you sure about this? Examine the following extremely simple test
case...

/************************************************** ********************
--create table

SQL> create table t0421(c number, d number);

Table created.

--load 100000 rows

SQL> begin
2 for i in 1..100000 loop
3 insert into t0421 values(mod(i,1000),i);
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> set autotrace traceonly

--get those rows where d=1 and it is the first rownum that matches the
criteria...

SQL> select d from t0421 where d=1 and rownum<2;


Execution Plan
----------------------------------------------------------
Plan hash value: 459506577

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 23 (5)|
00:00:01 |
|* 1 | COUNT STOPKEY | | | | |
|
|* 2 | TABLE ACCESS FULL| T0421 | 3 | 39 | 23 (5)|
00:00:01 |
----------------------------------------------------------------------------

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

1 - filter(ROWNUM<2)
2 - filter("D"=1)

--get only those rows that match the criteria, without the rownum
pseudo column

SQL> select d from t0421 where d=1;


Execution Plan
----------------------------------------------------------
Plan hash value: 2569877182

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 39 | 56 (2)|
00:00:01 |
|* 1 | TABLE ACCESS FULL| T0421 | 3 | 39 | 56 (2)|
00:00:01 |
---------------------------------------------------------------------------

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

1 - filter("D"=1)

Note
-----
- dynamic sampling used for this statement

--filter only those rows with the rownum of 1

SQL> select d from t0421 where rownum<2;


Execution Plan
----------------------------------------------------------
Plan hash value: 459506577

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)|
00:00:01 |
|* 1 | COUNT STOPKEY | | | | |
|
| 2 | TABLE ACCESS FULL| T0421 | 85354 | 1083K| 2 (0)|
00:00:01 |
----------------------------------------------------------------------------

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

1 - filter(ROWNUM<2)

Note
-----
- dynamic sampling used for this statement

************************************************** ********************/

The cardinality (and I/O, which I don't show), although reflecting a
full table scan, do very little I/O when I filter on the column, even
without an index. When I use only the rownum, the cardinality and I/O
increases.

Can you post the full trace?

Regards,

Steve

sorry, my answer wasn't really informative ...

how about something like this pseudocode:

declare
cursor c is
select 1
from yourtable
where yourcondition
;
dummy number;
begin
open c;
fetch c into dummy;
if c%found then dosomething
else dosomethingelse
end if;
close c;
end;

.... this should only fetch one row.

an exists-query also comes to mind:

select 1
from dual
where exists
(
select null
from yourtable
where yourcondition
)

"eye1h" <eye1h@yahoo.com> wrote in message
news:1145630740.287100.163020@u72g2000cwu.googlegr oups.com...
> Hi Terry,
>
> 1) This will also go through the full table scan to get rows (transnum
> = :Itransnum)
> 2) Then the results will logically sequenced and results which are less
> the rownnum 2 will be displayed.
>
> This has the same problems like the original query.
>
> Cheers
> Ivan
>
if transnum is unique and meant to be unique then add a unique index or make
it a hash table with a high number of hash entries. Having a single index
isn't going to affect it that much. (probably not as much as those full
table scans)
Jim