Tag: new features

Database, SQL | PLSQL

Oracle No Segment Indexes

 , , , ,

Did you know we have this? Yes, and this is since 11g.

As you know, the process of tuning SQL statements often requires the testing of alternate indexing strategies to see the effect on execution plans.

Adding extra indexes to large tables can take a considerable amount of time and disk space besides possibly changing known execution plans and possibly affecting all the application behavior.

This can be problematic when you are trying to identify problems on a production system. Some of those problems can be solved by using invisible indexes, but they still would require creation and segment allocation, but also being updated by the application itself.

To solve that, virtual indexes have no associated segment, so the creation time and associated disk space are irrelevant. In addition, it is not seen by other sessions, so it doesn’t affect the normal running of your system, similar to invisible indexes, but also don’t not even to be updated, as invisible indexes need to be.

To create the virtual index consists in simply add the NOSEGMENT clause to the CREATE INDEX statement, for example:

CREATE INDEX index_name ON table(column_name) NOSEGMENT;

However, to make the virtual index available we must set the _use_nosegment_indexes parameter. Another important mention is that the virtual indexes don’t appear in the USER_INDEXES view, but can be found in the USER_OBJECTS view.

Cheers!

Database, SQL | PLSQL

Oracle Virtual Columns

 , ,

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Virtual Columns

Since 11g is possible to create columns based on functions, not physically stored on database. See the example below:

create table sales
    (
       sales_id      number,
       cust_id       number,
       sales_amt     number,
       sale_category varchar2(6)
       generated always as
       (
          case
            when sales_amt <= 10000 then 'LOW' 
            when sales_amt > 10000 and sales_amt <= 100000 then 'MEDIUM' 
            when sales_amt > 100000 and sales_amt <= 1000000 then 'HIGH'
            else 'ULTRA'
          end
       ) virtual
   );

It’s also interesting to raise that starting on Release 2, virtual columns can be used as foreign keys. Should be used as FK, not sure… but can….

Cheers!