Performance of querying dual in Oracle

https://stackoverflow.com/questions/35030413/whats-the-performance-of-querying-in-dual-on-oracle-11g

Oracle 12c top 12 features

Watch a video with Tom Kyte about top features of Oracle 12c:

http://youtu.be/ekTTXoHBmWw

Oracle: Skip locked rows

In Oracle 11g you can

SELECT *
FROM EMPLOYEES
FOR UPDATE SKIP LOCKED;

Which means that you'll get rows that are not locked by another session.

Oracle 12c new features

See in the link below some new feature available in the upcoming version of Oracle DBMS, 12c.

http://www.oracle-base.com/blog/2012/10/06/oracle-openworld-2012-day-5/

What I like:

- Cascade for TRUNCATE and EXCHANGE partition.
- Multiple partition operations in a single DDL
- Online move of a partition(without DBMS_REDEFINTIION).
- Interval  + Reference Partitioning.
- If the optimizer notices the cardinality is not what is expected, so the current plan is not optimal, it can alter subsequent plan operations to take allow for the differences between the estimated and actual cardinalities.
- The stats gathered during this process are persisted as Adaptive Statistics, so future decisions can benefit from this.
- Information Lifecycle Management: Uses heat map. Colder data is compressed and moved to lower tier storage. Controlled by declarative DDL policy.

Oracle Database Concepts

(If you really search oracle database concepts see the doc from Oracle here)

• Sinonime: aceeaşi Mărie, cu altă pălărie.

• Tabelă partiţionată: o singură pălărie, mai multe Marii.

• Sistemul de drepturi: O Marie se duce la primărie, se intoarce fără hârtie. Normal, daca esti cetatean, poti obţine hârtia de la funcţionar. Totusi rolul generic de cetaţean nu-i de-ajuns, trebuie sa obţii dretpuri specifice, cu interventii unde trebuie. (In Oracle , daca ai obtinut drepturi asupra unui obiect printr-un rol, nu il vezi din proceduri stocate si alte constructii de acest gen. Trebuie sa ai grant specific pe obiectul respectiv.)

• Acces pe baza de index: te duci la Ion sa iti spuna unde e Maria.

• Acces pe baza de rowid: O cunosti foarte bine pe Maria.

• View materializat: Iti e de ajuns si o poză a Mariei.

• View: N-ai nicio poza, singura solutie e webcam-ul.

• Synonym translation is no longer valid: A plecat Maria, ai ramas doar cu pălăria.

Oracle order by using index

Here is a nice catch about using indexes in order by queryes:

http://stackoverflow.com/q/9684567/319875

Analytic functions

Analytic functions are nice:


stackoverflow: Conditional group by (group similar items)

As above, you can group by some rows and others not, in a single shot.

Oracle's minus has a bug?

No, it doesn't.
But look at the following situation:
You have two tables that must be identical(must contain same data).
You shoot a minus between them and get some rows. Few, but they exists.

You choose one row from the result of minus, and run
select * from table1 where col=key1
minus
select * from table2 where col=key1
and you get one row.

you run

select * from table1 where col=key1

union all

select * from table2 where col=key1

and you get two rows.

you run 

select * from table1 where col=key1

union

select * from table2 where col=key1

and you get one row!

so, the rows are not different!

How can that be possible???

You run explain plan for every query from above and it does what you asked in query.

Think a bit, and if you can't find the answer, scroll down to see it.

change default table or index tablespace

Is so simple as useful

ALTER TABLE table_name MODIFY DEFAULT ATTRIBUTES TABLESPACE new_tablespace;
ALTER INDEX index_name MODIFY DEFAULT ATTRIBUTES TABLESPACE new_tablespace;

For example, new partitions for local indexes will be created in the new tablespace when you add new partitions on the table.

Oracle - move partitioned tables to another tablespace

Just few days ago I've got a task to move all data from a tablespace to another - disk replacement.
My thought was: "simple job, excepting partitioned tables."
No, you do not need to recreate the tables(with all corresponding indexes) and make insert into new_table select * from old_table. I have 700 tables, few of them near 10GB, but many around 1GB.

First code I managed looked like this:


declare
   stmt varchar(1000);
begin
   for k in 0..189
   loop
      stmt:='ALTER TABLE table_name MOVE PARTITION P'||to_char(add_months(to_date('201102','yyyymm'),k),'yyyymm')|| ' TABLESPACE NEW_TABLESPACE';
      execute immediate stmt;

      stmt:='ALTER TABLE table_name MODIFY PARTITION P'||to_char(add_months(to_date('201102','yyyymm'),k),'yyyymm') ||' REBUILD UNUSABLE LOCAL INDEXES TABLESPACE NEW_TABLESPACE';
      execute immediate stmt;
   end loop;
end;

for tables partitioned by range in a monthly manner.