Re: Q: Oracle sqlplus - More Q's for Billy and others.

colocoloc_at_yahoo.com (ColoC) wrote

> However how fast would you see if I do a
>
> select
> /*+ FULL(x) PARALLEL(x,4) */
> count(*)
> from foo x

How long is a piece of string? Using a FTS may be much slower than a index FFS. Or not. It all depends.

> as mentioned in your previous post for a database of 10million+ per
> day database with partition technique setup for altogether 100 days?
>
> Does it improve a lot when I do query on a certain field without an
> index?

What is the slowest operation on a computer? Disk I/O.

And that becomes a big issue when dealing with large volumes of data.

There are two things you can do with VLTs (Very Large Tables) to make the disk I/O faster.

1. Process the data in parallel. Instead of having 1 process reading 1GB of data, you use 5 processes reading 128MB each in parallel. Simplistically of course as you need to consider factors like the size of the pipe between the disk array/channel and the CPU and so on.
2. Make the data to process less. Partitioning. Instead of having 1 process ploughing through 1GB of data, have it only process the data applicable - a mere 64MB of data. And if it make sense, do that in parallel too.

To answer your question about not using indexes. Yes. With partitioning, you can get away without using indexes. E.g. you use the partition criteria as part of query's criteria.

Let's say that we have a table partitioned on day. It contains a year's data - 365 partitions. Averaging 2 million rows per day.

Thus this will be fast:
SELECT stuff, more-stuff FROM foo
WHERE day = TO_DATE( '20030910', 'yyyymmdd' ) AND something = 'something else'
GROUP BY stuff

Even though DAY is not indexed, Oracle knows that the rows for that day are in a single specific partition. It will only process that single partition. This can also be done using parallel query.

This will be very slow (assuming no indexes are used): SELECT stuff, more-stuff FROM foo
WHERE something = 'something else'
GROUP BY stuff

Oracle will need to look at all 365 partitions when doing this query. Using a full table scan. Even when done in parallel, it will take some time.

My suggestion is to get yourself your own R&D database and start to play around with these. There is IMO no substitute for getting your hands dirty and trying and experimenting yourself.

--
Billy