Friday, July 1, 2011

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.




Wednesday, June 1, 2011

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.

Thursday, May 19, 2011

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.

Tuesday, May 17, 2011

miroase ca la munte

Maria, până sa implinească 3 ani, iarna asta.
Am luat un şampon diferit de cel cu care era/eram obişnuiţi.
Când îl folosim prima dată, Maria comentează: "Ăsta e ca la munte". Noi nu am înţeles nimic, evident. Nu scria pe el mountain, nu era cu cine ştie ce arome... ce vrea sa zică figurina asta de Maria?

Ne prindem noi după ceva timp cum stă treaba. Vara trecută, când am fost la munte, am luat la repezeală din supermarket un şampon de copii, pe care l-am folosit la munte. Era acelaşi şampon pe care il foloseam acum şi maria l-a recunoscut dupa... miros. Maria a asociat mirosul cu muntele sau... în sfârşit, şi-a adus aminte ceva cu şase luni în urmă. Pe atunci avea până în doi ani şi jumătate, şi intre timp nu am folosit un astfel de şampon.

Thursday, April 21, 2011

aniversare :)

Acum observ ca scriu de mai mult de un an pe blogul "propriu si personal".
Funny.

29 de posturi in un an. Carevasazica unul la doua saptamani.

Salut viitorii mei cititori :P

Ca acu nu ma citeste nimeni. E exact ceea ce s-a vrut intial. Un blog=web log = jurnal pe internet. Nu trebuie neaparat sa ai cititori. E linistitor.

Wednesday, February 9, 2011

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.

Wednesday, February 2, 2011

Zeitgeist Filmul

Exista o productie foarte apreciata si cautata printre care se cred intelepti si considera ca ei "gandesc". Multi il recomanda dupa ce l-au vazut("Va zgudui din temelii tot ce stiati pana acum" si "Nerecomandat persoanelor religioase") si multi zic "M-a pus pe ganduri...".
Oamenii sunt foarte multumiti cand afla despre religie ca merita aruncata la cosul de gunoi, si ca tot ce inseamna guvernare, banci si altele de genul acesta, sunt facute doar pentru a subjuga marea masa a oamenilor comuni. Totul este o conspiratie. Si cum "Veti cunoaste adevarul si adevarul va va face liberi", e o mare eliberare sa stii asta.

Zeitgeist, asa cum mentioneaza si articolul dedicat de pe wikipedia, face N afirmatii fara a le dovedi, altele neverificabile, si este amplu criticat de cei avizati, pe diferite canale de comunicatie.
L-am vazut pe primul dintre ele acum vreo 2-3 ani; acum a aparut al treilea.
Eu nu sunt un cunoscator in materie de finante/circuitul banilor in natura, dar haideti sa va arat ca orice bolovan poate sa realizeze(daca si are bunavointa) ca filmul este un bullshit-o aiureala.
De exemplu, se face afirmatia ca nu ar trebui sa se plateasca taxe la stat.
Pai daca nimeni nu plateste taxe, nu am avea stat: cine sa plateasca gunoierii, judecatorii, administratia centrala? Cine sa plateasca granicerii? Cine sa plateasca politia? Sau ei se vad in o lume imaginara perfecta in care toti oamenii sunt buni, nimeni nu incalca bunul-simt(ca legi nu ar exista - cine sa le faca) si toti sunt de acord asupra standardelor - nescrise? Sau ei cred ca vor fi unii care sa faca astea pe baza de voluntariat? Da, eventual facem niste donatii, ca sa isi intretina familia. Wake up, care e diferenta?
Stiti cum e? Daca un om vine la tine si spune aseara l-a vazut pe Mos Craciun cum a coborat pe horn si i-a adus cadouri baietelului lui de 4 ani, dupa care a plecat zburand cu trasura propulsata de reni, dupa care imi spune el sigur ca peste trei ani Euro va fi 8 RON, tu ce faci? Vinzi casa si o transformi in Euro? Trebuie sa fii nebun.
Asa si cu filmusorul asta. Daca face o greseala colosala, nu merita sa ii acorzi credit in lucruri pe care nu le cunosti.

Cat despre partea cu religia, ma pot da cunoscator si sa va spun ca ABEREAZA. Habar nu au despre crestinism, care are ca sursa iudaismul. Fac legaturi care nu exista si trag concluzii care se bat cap in cap cu ce afirma biblia si dovezile istorice.
Ca o paranteza, cu o sa apar niciodata catolicismul. A facut gafe imense. Si va spun sigur, nu se bazau pe Biblie. Erau departe de ea.

Revenind la film, pe mine nu m-a zdruncinat, nu-mi venea sa cred cat de rau-voitori sunt si cum dezinformeaza direct folosindu-se de chestiuni necunoscute publicului larg. Inchei cu cateva citate de pe wikipedia:
"the use of unreferenced and undated assertions, and standard film-making propaganda techniques."
"Thus legitimate questions about what happened on 9/11, and about corruption in religious and financial organizations, are all undermined by the film’s determined effort to maximize an emotional response at the expense of reasoned argument."
Si cateva observatii despre erori fatise din partea intai(cea cu religia):
"Examining some of the specific claims made by the film, Forbes points out that while there are parallels between the story of Jesus and many ancient mythological figures, many of the ones mentioned in the film are false, as are other aspects of the film's description of these myths. Forbes states that there is no evidence in Egyptian sources that Horus' mother Isis was a virgin, and says that Ra was the Egyptian god of the sun, not Horus. Similarly, neither Krishna nor Dionysus nor Attis were ever said to be born of virgins, as Krishna was the eighth child of his parents, Devaki and Vasudeva, and Dionysus' mother, Semele, had slept with Zeus. Forbes asserts that Horus was not adored by three kings, and that neither he nor Attis were crucified nor resurrected. Forbes and interviewer John Dickson, founder of the Centre for Public Christianity, took issue with what they perceived as an argument centered on the homophony between the words "Sun" and "Son" in regards to Jesus, with Forbes dismissing this point as a pun, and pointing out that those words are not homophonic in ancient Egyptian, Latin or Greek. Forbes also points out that neither Horus, Attis nor Jesus were born on December 25, as the ancient Egyptian calendar did not include the month of December found in the Latin calendar, and that the date of Christmas is a celebratory tradition historically derived from Sol Invictus and Saturnalia, rather than the Bible.[28]
Forbes also criticizes the movie's use of Roman sources to suggest that Jesus did not exist, noting that the list of supposed contemporaneous historians alleged by the film to have not mentioned Jesus is actually a list of geographers, literature professors, poets, philosophers and writers on farming or gardening, who would not be expected to mention him, and that the modern sources cited in the film are either experts in fields other than ancient history, such as German literature, or uncredentialed amateur Egyptologists. Forbes challenges the film's allegation that Josephus' mention of Jesus was doctored by pointing out that Josephus actually mentions Jesus twice, and that only one of these mentions is believed by scholars to have been doctored in the Middle Ages, in order to change an already existing mention of him (see Testimonium Flavianum). Forbes also argues that while Emperor Constantine I legalized Christianity, it was Theodosius I who made it compulsory later in the 4th century, and that contrary to the film's thesis, Constantine did not invent the historical Jesus, as early records show that his historicity was already a key element of early Christianity prior to Constantine's conversion to it."