DB Readings

I've got this list from Cristian Vasile:
https://github.com/rxin/db-readings

Nice Problems(2)

See here a small nice SQL problem with a nice answer from me:

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

Nice problems

If you like challanges see these problems on StackOverflow:

Rectangles:
http://stackoverflow.com/q/7706664/319875

Resequence items after a delete:

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

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.

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.

SQL programming style

Am terminat de citit de curand cartea lui Joe Celko, SQL Programming Style.
Cartea are cateva portiuni faine si ca structura este ok, si ca subiecte chiar, insa per ansamblu lasa un iz de... nu stiu cum sa ii zic.
Frustrarea mea a fost ca de multe ori a trebuit ca citesc povesti legate de coduri de bare americane si chiar mai rau: s-au facut in carte muuulte referiri la programare cu punchcards. Imi tot repeta ca multi au ramas cu obisnuinte(a se citi sechele) din vremea in care programau cu punchcards, si Joe tot incearca sa ne dezvete pe noi in carte, ca nu este asa in SQL si, in general in zilele noastre nu mai avem restrictiile cu pricina.
Insa cu tot respectul, nu ca sunt vremuri de mult apuse, sunt 30 de ani. O generatie. E ca si cum ai vorbi inginerilor de la Toyota de motorul cu aburi: Nici nu ii intereseaza cum functioneaza(desi stiu) si nici nu au sechele.
Mai degraba Joe Celko are sechele, si cu greu poate sa se dezbare de stilul punchcards.
Ok, cred ca am exagerat cu critica. Jumatate din carte e utila. Cred ca, asa cum imi zicea si Gabi, ca e o carte nu pt oameni strict specializati pe SQL(desi le poate fi utila, depinde) ci pt oameni care au de-aface cu SQL si trebuie sa-si imbunatateasca abordarea.