Thursday, September 19, 2013

[how to] Oracle Equivalent of PostgreSQL CLUSTER

[how to] Oracle Equivalent of PostgreSQL CLUSTER

Oracle Equivalent of PostgreSQL CLUSTER

Posted: 19 Sep 2013 04:27 PM PDT

PostgreSQL has the CLUSTER command to group rows physically on disk. By grouping on information where "neighboring" rows (for lack of a better term) are often accessed together, performance improves since fewer disk blocks need to be read in a given query. Does Oracle have anything similar? Would it even help performance on a large table that is almost never updated if there is such an option?

What does maintenanceMode -7 mean on a mongo primary?

Posted: 19 Sep 2013 03:06 PM PDT

I've recently promoted a new member to primary of a mongo replicaset. Things seem to be running smoothly for several days and am not seeing any application symptoms of any problems. However peeking at rs.status() today I noticed it's listing maintenanceMode -7. I didn't think this should be possible as it's the primary. I'm having trouble finding anything useful in the docs on what this means.

What does this mean?

    {          "_id" : 26,          "name" : "mongohost5:27032",          "health" : 1,          "state" : 1,          "stateStr" : "PRIMARY",          "uptime" : 106966,          "optime" : Timestamp(1379627877000, 77),          "optimeDate" : ISODate("2013-09-19T21:57:57Z"),          "maintenanceMode" : -7,          "self" : true      }

Updating a local table with a per-row count(*) which is an aggregate of inner joins on remote server

Posted: 19 Sep 2013 07:55 PM PDT

I have a table of around 350m rows on a linked server in which I added an additional INT column to serve as a count(external_identification) of records as the result of a join on PACKAGE and DOC2. Since the tables are so large I'd like to process the update in batches, both so I can gauge progress and to avoid creating huge temp tables. Each column is indexed.

Would this be a good example where a CTE comes into play? Quite honestly they confuse me with the way they need to be written, it's hard to visualize...

The tables are structured as:

ServerA (utility SQL server)
Table: CLIP_IDs
Columns: Package_UUID nvarchar(255), MessageExtractState tinyint, [count] int (350m rows)

ServerB (main database server)
Table: PACKAGE
Columns: Package_UUID nvarchar(255), Package_id bigint (650m rows)
Table: DOC2
Columns: External_Identification nvarchar(255), Package_id bigint (2b rows)

Both SQL servers are linked both ways, if initiating the query from one is more efficient. I have a feeling issuing the query from ServerA will be, as it seems like the execution plan offers less remote queries.

I stopped the query below after 26 hours because I think I have a syntax logic error. Can someone explain what it is and offer any suggestions please?

Executed from ServerA:

DECLARE @rowsUpdated INT    SET @rowsUpdated = 1    WHILE (@rowsUpdated > 0)  BEGIN      UPDATE CLIP_IDs      SET [Count] = x.[count]      FROM (          SELECT TOP 50000 c.package_uuid              ,count(d.external_identification) AS [count]          FROM CLIP_IDs c          INNER JOIN ServerB.DATABASE.dbo.package p(NOLOCK) ON c.package_uuid = p.package_uuid          INNER JOIN ServerB.DATABASE.dbo.doc2 d(NOLOCK) ON p.package_id = d.package_id          WHERE c.messageextractstate = 1              AND c.[count] IS NULL          GROUP BY c.package_uuid          ) x        SET @rowsUpdated = @@rowcount        PRINT N'Finished set of rows: ' + convert(VARCHAR, getdate(), 120)  END

Index for ORDER BY on aggregate in MySQL

Posted: 19 Sep 2013 06:39 PM PDT

I'm working with the following query:

SELECT *, SUM(entries_count) AS entries_count FROM leads  WHERE promo_id IN (1, 2, 3)  GROUP BY user_id  ORDER BY entries_count DESC  LIMIT 25 OFFSET 100;

It's quite fast without the ORDER BY clause, but very slow with it. I have a few compound indexes, but it only seems to want to use the index on user_id and that's it. Here's the EXPLAIN output:

| 1 | SIMPLE | leads | index | index_leads_on_promo_id_and_user_id,index_leads_on_promo_id | index_leads_on_user_id | 4 | NULL | 219647 | Using where; Using temporary; Using filesort |

I find it a bit odd that it's using a key which isn't even listed under "possible keys". Anyways, is there any way to provide a better index to improve this query, or will I need to denormalize with pre-calculated rollups?

EDIT: Here's the CREATE TABLE statement:

CREATE TABLE `leads` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `user_id` int(11) NOT NULL,    `promo_id` int(11) NOT NULL,    `entries_count` int(11) NOT NULL DEFAULT '0',    PRIMARY KEY (`id`),    UNIQUE KEY `index_leads_on_promo_id_and_user_id` (`promo_id`,`user_id`),    UNIQUE KEY `index_leads_on_user_id_and_promo_id` (`user_id`,`promo_id`),    KEY `index_leads_on_user_id` (`user_id`),    KEY `index_leads_on_promo_id` (`promo_id`),    KEY `index_leads_on_user_id_and_entries_count` (`user_id`,`entries_count`),    KEY `index_leads_on_user_id_and_promo_id_and_entries_count` (`user_id`,`promo_id`,`entries_count`)  ) ENGINE=InnoDB AUTO_INCREMENT=413235 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

As you can see, I've added several indexes just to experiment and see if they'd improve the query's performance, but so far no dice.

Can I move the undo log outside of ibdata1 in MySQL 5.6 on an existing server?

Posted: 19 Sep 2013 02:00 PM PDT

I've been growing concerned about the large size of ibdata1 that can never shrink even when using file-per-table on innodb

Moving the undo log files outside seemed logical but this procedure seems rather complicated:

http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html#innodb-undo-tablespace

Does anyone have hands-on experience accomplishing this on a live server?

This line makes it sound like it is impossible once mysql is already installed and running:

this feature can only be enabled when initializing a MySQL instance

So too late now?

how to fetch multiple rows with same field value from a mysql database using php [migrated]

Posted: 19 Sep 2013 11:56 AM PDT

|touser| fromuser |  msg  |  |  A   |    B     | hi    |  |  A   |    B     | hello |  |  C   |    D     | bye   |  |  C   |    E     | hey   |

when i use following query in mysql workbench it shows the desired result that is all the rows with given name:

select * from db.table1 where touser in ('A');

output:

|touser| fromuser |  msg  |  |  A   |    B     | hi    |  |  A   |    B     | hello |

but when i pass query from php commands the resultant array contains only first record

<?php   //connection establishing commands   $sql="select * from db.table1 where touser in ('A')";     $result=mysqli_query($link, $sql);     $data=mysqli_fetch_array($result,MYSQLI_NUM);     print_r($data);     //other stuff;   ?>

output:

    Array ( [0] => A [1] => B [2] => Hi )

am I missing something in the php commands?

Paging and grouping using CTE?

Posted: 19 Sep 2013 09:37 AM PDT

Given the following code, from the answer to this question, how can I adapt it for paging? I've read that Common Table Expression is a good way to do paging, but since I implemented CTE to solve a problem that was already over my head, I really have no idea where to start in merging the two solutions.

;with cte as  (    SELECT DISTINCT d.AppId,      d.AppName,      d.AppType,      Tags = STUFF((SELECT ', ' + t.TagName                    FROM AppTags t                    where d.AppID = t.AppID                    FOR XML PATH (''))                    , 1, 1, '')     FROM AppDetails d    WHERE d.AppID = '1'  )  select *  from cte  where tags like '%test1%'

Dynamic Joining Across Databases

Posted: 19 Sep 2013 06:38 PM PDT

I have a multi-database architecture with a single database for each client and a separate database to handle system-wide data. In this separate database, I have an 'Updates' table where a row is updated every time certain data from the client databases are updated. The 'Updates' table is setup somewhat like this:

DbName     CustomerId     ClientA    1             ClientB    1

I would like to join this data to a table in the database denoted in the 'DbName' column and end up with something like this:

DbName     CustomerId     CustomerName  ClientA    1              Bob Jones  ClientB    1              John Smith

What is the cleanest and most efficient solution for this problem?

RegEx with update query my sql

Posted: 19 Sep 2013 10:29 AM PDT

i need to use a mysql update query with regex so my example is 

update table_name set coulmn_name = replace(coulmn_name, 'regex', '');

i will explain a specified thing i need to do. my column is contained some text & some html tags i need to delete all img tags from this coulmn what can i do to achieve this ?

this is my ReGex

/</?(?i:img)(.|\n)*?>/g

but i don't know how to make this any help please

Can I reset the ibdata1 and ib_logfile files when innodb_file_per_table is already in place

Posted: 19 Sep 2013 10:39 AM PDT

I am running into an issue where sensitive data was not encrypted properly at the application level of our production system.

Even though the guilty records have been removed or corrected, scanning still reveal the records in the .ibd file, as well as the ibdata1 and ib_logfiles.

I have optimized the affected table and so the .ibd file is clean now; but I still have the issue of the ibdata1 and log files.

Doing a full mysqldump of all databases is a last resort as the data set if quite large. Since we are already using per table tablespaces, can I simply stop mysql, move the ib files and then restart and reset the files in that manner?

Scheduling duplication of two tables across instances

Posted: 19 Sep 2013 11:59 AM PDT

In my SQL Server 2012 database I have two tables:

  • [tbl_Customers] - about 30 rows
  • [tbl_Users] - about 10,000 rows

I need to schedule the two tables above to be copied to a new instance, say every 12 hours. I am considering setting up a SQL Azure account to act as the second instance.

[tbl_Users] is a popularly-accessed table, with CRUD operations being performed quite a lot, therefore the data changes often. It doesn't matter too much about those changes being replicated on the second instance immediately (but it would be nice if possible if the option allows).

Can anyone recommend which approach they might take to achieve this? I'm new to this type of thing so please don't hold back with the simplicity of your answers...!

I have SQL2012 Standard on my dev machine (but no SSIS at the moment although I could install it), but don't know if my existing SQL host and/or Microsoft have SSIS on their offerings.

Excessive replication snapshot size causing failure

Posted: 19 Sep 2013 02:42 PM PDT

I am searching for an explanation of a replication snapshot being in excess of 5 times what we're predicting

Following a replication failure we are attempting to reinitialise subscriptions with a new snapshot. The size of the database is 135GB, one table within the database is 60GB - we have excluded this article from the publication. This meant we had an expectation of snapshot size being ~75GB.

We've had several attempts at creating the snapshot and these have failed due to lack of disk space. Last night the snapshot filled a disk with 340GB of free space.

I 'd welcome any explanation for this huge size of snapshot.

In my examination of possible causes I note that the snapshot format is set to "Character - Required if a publisher or subscriber is not running SQL Server". This despite the fact that at both ends of this transnational replication is native SQL Server. Is there a different in size between formats?

Thanks in advance.

Native mode file Size:105 MB  C:>bcp IVM_ArchiveTest.dbo.Event out D:\NOBACKUP\UseOnce\EventNative.dat -T -n   218977 rows copied. Network packet size (bytes): 4096 Clock Time (ms.)   Total : 7878 Average : (27796.01 rows per sec.)     Character mode file Size:66 MB   C:\>bcp IVM_ArchiveTest.dbo.Event out D:\NOBACKUP\UseOnce\EventChar.dat -T -c   218977 rows copied. Network packet size (bytes): 4096 Clock Time (ms.)   Total : 1654 Average : (132392.38 rows per sec.)

Query getting periodically stuck in 'copying to tmp table' state, never completes

Posted: 19 Sep 2013 02:48 PM PDT

I am running Wordpress on a dedicated server with a MySQL backend. I have a query that usually takes <1 second to execute, but periodically, this query will get stuck in a 'copying to tmp table' state and stay that way indefinitely until it is either killed or until mysqld is restarted. After restarting mysqld the problem goes away, the (identical) query once again takes <1 second to execute. This leads me to believe this is a configuration problem.

How do I go about solving this problem? The query itself is not too intensive, and my server is not experiencing any sudden traffic spikes. The tables themselves are all InnoDB format.

Here is my my.cnf: http://pastebin.com/9UMPxfAr

The query:

SELECT ctt.term_id AS catid, p.ID, p.post_title AS title, GROUP_CONCAT(tt.term_id) as terms_id          FROM (SELECT * FROM wp_posts WHERE post_status = 'publish' AND post_type = 'post' AND post_date > '2013-09-09 17:00:00' AND post_date < '2013-09-10 08:14:00') AS p                  JOIN wp_postmeta AS pm                          ON (p.ID = pm.post_id AND pm.meta_key = 'wpo_sourcepermalink')                  JOIN wp_term_relationships AS ctr                          ON (p.ID = ctr.object_id)                  JOIN wp_term_taxonomy AS ctt                          ON (ctr.term_taxonomy_id = ctt.term_taxonomy_id AND ctt.taxonomy = 'category' AND ctt.term_id IN ('8','314','6'))                  JOIN wp_term_relationships AS tr                          ON (p.ID = tr.object_id)                  JOIN wp_term_taxonomy AS tt                          ON (tr.term_taxonomy_id = tt.term_taxonomy_id AND (tt.taxonomy = 'post_tag' OR tt.taxonomy = 'post_author'))          GROUP BY tr.object_id          ORDER BY pm.meta_value ASC;

An EXPLAIN of the query: http://pastebin.com/m5ndBfVX

And the output of "SHOW ENGINE INNODB STATUS" when a query is stuck in the 'copying to tmp table' state: http://pastebin.com/h0xv4Sfa

Indices of some of the tables in the query:

http://pastebin.com/XtTBL7Xr

http://pastebin.com/NamVSu4F

SSRS 2008. subreport page breaking issue migrating SSRS 2005 reports over to 2008

Posted: 19 Sep 2013 07:03 PM PDT

I'm moving over several hundred reports that were created in SSRS 2005 over to an SSRS 2008 server, and most run fine when copying the .rpt files over, but the reports that have sub-reports don't page break correctly and the reports look terrible. Does anyone know of a way to correct this issue or do all of these reports need to actually be re-created from scratch in SSRS 2008?

Help with Oracle Pipelined function

Posted: 19 Sep 2013 08:22 PM PDT

I have a pivot where I have a contract with tank numbers separated by ; and I've been given this example to pivot them down into rows (which works):

SELECT oks_c.contract_number,          oks_l.line_id,         oks_l.tank_numbers, column_value AS TANK_NUMBER     FROM oks_contract_header oks_c   INNER JOIN oks_contract_lines oks_l       ON oks_l.contract_number = oks_c.contract_number AND          oks_l.item_name LIKE '%.55201'      , table(str2tbl(oks_l.tank_numbers,';')) acbb  ORDER BY oks_c.contract_number,            oks_l.line_id,            TANK_NUMBER

Here's the pipelined function:

CREATE OR REPLACE function DBRAJAH.str2tbl( p_str in varchar2, p_delim in varchar2 default ',' ) return   str2tblType  PIPELINED  as      l_str      long default p_str || p_delim;      l_n        number;  begin      loop          l_n := instr( l_str, p_delim );          exit when (nvl(l_n,0) = 0);          pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) );          l_str := substr( l_str, l_n+1 );      end loop;      return;  end;

But I'm trying to understand why it works. Particularly how the pipelined function results are not cross-joined?

I'm familiar with outer apply and table-valued functions in SQL Server, but this seems to be quite different - or is this really an OUTER APPLY?

startup mount issues with Oracle

Posted: 19 Sep 2013 03:22 PM PDT

I am a newbie with Oracle so bear with me.

I am trying to simply shutdown and restart an Oracle db instance on a Windows Server 2008 machine (Oracle 11.2.0). Logged in via sqlplus, I issue the command shutdown immediate;. I get the

Database closed.    Database dismounted.    ORACLE instance shut down

reponses. I exit out of sqlplus, and try to log back in (ORACLE_SID is set correctly) so I can issue the startup mount; command, but I get the 

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

error. All of the Windows services involving my database dantest3 are started. Doing research, I found the oradim command, but while that starts my database and allowing me to login, that is the full startup, and I want to only be in "mount" mode.

I am logging in with the following command - 

sqlplus sys/[passwordforacct]@dantest3 as sysdba

If I try the simple sqlplus / as sydba, I get a 

ORA-12560: TNS:protocol adapter error

oradim command - 

oradim -start -sid dantest3

Requested variables -

TNS_ADMIN = C:\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN  ORACLE_SID = dantest3  ORACLE_HOME_11gR2 = C:\Oracle\product\11.2.0\dbhome_1

Any advice or suggestions would be greatly appreciated!

Speeding up a NOT IN subquery

Posted: 19 Sep 2013 05:22 PM PDT

Good evening,

I'm currently up to try to improve the performance of some of my queries. As far as I know, statements like "IN" or "NOT IN" are even faster with a large amount of values if a subquery on an indexed field without conditions is used.

SELECT * FROM table1 WHERE field1 NOT IN (SELECT index_field FROM table2)

When it comes to use conditions at table2, the query becomes realy slow on a large amount of data.

SELECT *  FROM table1  WHERE      field1 NOT IN (          SELECT              index_field          FROM table2          WHERE              user_id = '2'      )

I was thinking about using LEFT Join but when I need to to the filtering for the user_id, it also becomes slow.

How may I solve this problem? Currently, I've no clue about that.

The original query looked sth. like this

SELECT      i.*  FROM stream_item si  LEFT JOIN user__publisher_item ui      ON ui.user_id = 2      && ui.item_id = si.item_id  INNER JOIN item i      ON i.id = si.item_id  WHERE       si.stream_id IN (5,7,8,9,19,24,29,42,43,44)      && ui.id IS NULL || (          ui.`read` = 0 || ui.saved = 1      )  GROUP BY      si.item_id  ORDER BY      si.`found` DESC  LIMIT 0,10  
id      select_type     table   type        possible_keys                                                                           key                 key_len     ref                 rows        Extra  1       SIMPLE          si      index       PRIMARY,stream_2_item,stream_id_found                                                   stream_2_item       4           \N                  663236      Using temporary; Using filesort  1       SIMPLE          ui      eq_ref      user_id_item_id,user_2_item,user_id_read_saved_hidden,user_id_saved,user_id_hidden      user_id_item_id     8           const,si.item_id    1           Using where  1       SIMPLE          i       eq_ref      PRIMARY                                                                                 PRIMARY             4           si.item_id          1

I have a table which represents the n:m relationship between stream and items. An unique item can be accessible via multiple streams. Based upon this i have a state-table for user dependent states of an item. Currently, the user-state-entry will only be generated, if the item was read or saved the first tim. Otherwise, there is no entry in the user_item table. In the beginning, this query was fine but now, it takes a lot of time. Even if I leave out the "INNER JOIN" it takes 7 seconds on the current tables with around 500k rows each.

The table-structure in the background is as follows:

  • stream
  • stream_item //n:m relation between stream and items
  • item //unique items
  • stream_group //user-defined groups of multiple streams
  • stream_group_streams //_n:m relation between stream_groups and streams_
  • user
  • user_item //state-table that holds the user-dependent states of an item - if there is no state, no row exists for an item

I already asked this question at Stackoverflow but added my 2 Edits with further information to late so that I haven't got an answer anymore.

Migration to databases Domain Users do not have access

Posted: 19 Sep 2013 04:22 PM PDT

I migrated databases to new servers, however the applications that were previously used with the databases are failing to load. I have changed the connections and etc. The jobs also seem to be failing. I have a domain account who is the job owner. However, when I try to execute the job under my User name i get the following error:

Executed as user: NT AUTHORITY\SYSTEM. Login failed for user.....[SQLSTATE 28000) (Error 18456).

Is this related to Domain Users not having appropriate read and write access to the database. Also how would I give All domain users permissions to execute stored procedures.

Creating a global temp table in MySQL

Posted: 19 Sep 2013 01:22 PM PDT

I am working on a MySQL stored procedure.

I need to create a temp table and I want to access this temp table whenever I execute this stored procedure.

I know we can't access the temp table in another request in MySQL.

Is there a way to create a temp table like globally, or how can I access the temp table across the multiple requests?

Which one to use? InnoDB Replication or MySQL Cluster?

Posted: 19 Sep 2013 10:22 AM PDT

I am building a url shortener and I use InnoDB as the storage engine for link data. I will start with a single VPS containing both application instance and MySQL database instance on the same Virtual Machine. I want the application to be easily scalable, I want to be able to add new nodes and make the system perform better as easily as possible when needed.

I have been reading about MySQL Cluster and MySQL Replication, but I haven't been able to decide on which one to use. You can imagine that (a popular) url shortener will be both write and read intensive. What is the structure that you would use in such a case? Would you go for cluster or replication?

Then based on the choice of cluster or replication, what is the infrastructure/configuration that I am supposed to have in order to be able to expand from a single innoDB engined Database to a cluster or replication structure? I want to start correctly, I don't want to be stucked in a situation where I can not expand/improve the database system when I need to.

Thanks A lot

sr.

MySQL server crashed.

Posted: 19 Sep 2013 07:22 PM PDT

Help! I managed to crash MySQL last night. I am on a Mac using the native version that came with Mountain Lion. I was upgrading from 5.5 to 5.6. I have followed instructions in this forum to delete the installation, but trying to re-install 5.5 says that there is a newer version and won't install. Trying to install 5.6 fails. I found this error in the console:

4/21/13 10:16:56.000 PM kernel[0]: exec of /Volumes/mysql-5.6.11-osx10.7-x86/mysql-5.6.11  osx10.7-x86.pkg/Contents/Resources/preflight denied since it was quarantined by TextEdit    and created without user consent, qtn-flags was 0x00000006

Help me please ?? I am stuck and in a world of hurt and despair.

Oracle: How to gather stats in a logical standby database?

Posted: 19 Sep 2013 12:22 PM PDT

I have a Primary and a (logical) Standby Database. The Schema 'APP' gets synced. Now a User has performance issues running a query against some tables in this 'APP' schema of the Standby Database. On the Primary the same query executed well after gathering table (DBMS_STATS.GATHER_TABLE_STATS) stats.

How to refresh statistics on the standby-side? The DBMS_STATS.GATHER_TABLE_STATS gives a ORA-16224.

With 

SELECT table_name,last_analyzed FROM dba_tables WHERE table_name IN ('TAB1','TAB2');

I can clearly see that the standby stats are a bit too old ;)

SELECT guard_status FROM v$database;    GUARD_STATUS  ------------  STANDBY

Running a TPC-C Benchmark Without sleep() i.e. key-in + think time

Posted: 19 Sep 2013 11:22 AM PDT

We are running a TPC-C benchmark against a PostgreSQL 9.2 server using JdbcRunner-1.2 implementation. During first few tests we were not getting a smooth (that is, without sudden spikes down to almost 0 from 300 that we got at times) TPS graph even in supposedly steady state. During these tests we had not specified the sleeptimes for transactions (there are different sleep times recommended for different transaction types). So, by default all agents (for example, 100) continuously ran a mix of five transaction types without any sleeps in between. Later, we found out that we could in fact specify the sleep times; and when we did we could see a smoother graph without much variations.

So, the question is, if not specifying the sleep could be the real reason behind the bad results in first few tests?

Deleting folders after Oracle upgrade

Posted: 19 Sep 2013 02:22 PM PDT

I upgraded from Oracle 10g to Oracle 11g in Windows Server 2003.

New Oracle home : C:\app\oracle\product\11.2.0\db_1

Old Oracle 10g was installed on: C:\oracle\product\10.2.0\

Questions

  • Is the 10g folder is still useful?
  • Could I delete the 10g folder without a problem?

How to optimize a log process in MySQL?

Posted: 19 Sep 2013 06:22 PM PDT

In my project, I have about 100.000 users and can't control their behavior. Now, what I would like to do is log their activity in a certain task. Every activity, is one record which includes columns like user_id and some tag_id's.

The problem I have, is that these tasks in some cases can go up to 1.000.000 per year per user. So if I would store all these activities in one table. that would obviously become very big (=slow).

What is best to do here? Create a single table per user (so I have 100.000 log tables) or put all these activities in one table? And what kind of engine should I use?

One important thing to note: Although i simplified the situation a bit the following doesn't look normal, but users can also change values in these tables (like tag_id's).

Cannot change the Delete Rule of a self referencing foreign-key-relationship to cascade delete

Posted: 19 Sep 2013 01:05 PM PDT

I have the following table

[dbo].[myObj]  (      [ID] [int] IDENTITY(1,1) NOT NULL,      [fk_ParentID] [int] NULL  )

and I have a relationship from fk_ParentID to ID.

I want to change the Delete Rule in SQL Server Management Studio under the Window "Foreign-Key Relationships"->Insert and Update Specifications->Delete Rule to ON Delete Cascade, but it is not possible.

Can you tell me why, and how I can make this possible?

Oracle listagg forces SORT (GROUP BY) execution plan

Posted: 19 Sep 2013 01:45 PM PDT

I have the following query

SELECT /*+ USE_HASH_AGGREGATION */ id  --, count(id)  , listagg(type, ', ') within group (order by null) types   FROM test  group by id

Both type and id are short strings. The execution plan for when I comment out listagg and leave count aggregation in is a HASH GROUP BY (even without the hint) and works fast. With the listagg aggregation Oracle always chooses SORT GROUP BY which is an order of magnitude slower. Is there any reason for that?

Is database normalization dead?

Posted: 19 Sep 2013 03:50 PM PDT

I've been brought up old school - where we learned to design the database schema BEFORE the application's business layer (or using OOAD for everything else). I've been pretty good with designing schemas (IMHO :) and normalized only to remove unnecessary redundancy but not where it impacted speed i.e. if joins were a performance hit, the redundancy was left in place. But mostly it wasn't.

With the advent of some ORM frameworks like Ruby's ActiveRecord or ActiveJDBC (and a few others I can't remember, but I'm sure there are plenty) it seems they prefer having a surrogate key for every table even if some have primary keys like 'email' - breaking 2NF outright. Okay, I understand not too much, but it gets on my nerves (almost) when some of these ORMs (or programmers) don't acknowledge 1-1 or 1-0|1 (i.e. 1 to 0 or 1). They stipulate that it's just better to have everything as one big table no matter if it has a ton of nulls "todays systems can handle it" is the comment I've heard more often.

I agree that memory constraints did bear a direct correlation to normalization (there are other benefits too :) but in today's time with cheap memory and quad-core machines is the concept of DB normalization just left to the texts? As DBAs do you still practice normalization to 3NF (if not BCNF :)? Does it matter? Is "dirty schema" design good for production systems? Just how should one make the case "for" normalization if it's still relevant.

(Note: I'm not talking about datawarehouse's star/snowflake schemas which have redundancy as a part/need of the design but commercial systems with a backend database like StackExchange for example)
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog