Classics Forum

RE: SERIAL to INTEGER key joins vs. CHAR to CHAR.

Posted By: Zev Berezin
Date: Tuesday, 9 March 2010, at 3:18 p.m.

In Response To: Re: SERIAL to INTEGER key joins vs. CHAR to CHAR. (Pankrich Khanthasa-ard)

Are you sure about that last paragraph? I know when adding data it will not maintain the physical order of the cluster, and therefore over time an alter index indexname to cluster should be executed.

I am not sure when updating data.

Zev Berezin

-----Original Message-----
From: classics-bounces@iiug.org [mailto:classics-bounces@iiug.org] On Behalf Of Pankrich Khanthasa-ard
Sent: Monday, March 08, 2010 11:01 AM
To: classics@iiug.org
Subject: Re: SERIAL to INTEGER key joins vs. CHAR to CHAR. [3518]

I just have a little bit doubt why your CUSTOMER.cust_name can be unique. I
mean it's possible to have many "John" in your villege.

Anyway, why don't you try in simplest way.
- Primary key with unique index on CUSTOMER.cust_id
- Duplicate index on CUSTOMER.cust_name
- Foreign key with normal index on LOANS.cust_id

Don't forget about the cluster index will generate more cost when you try to
modify data because DBMS has to maintain the physical data ordered.
So it will be better to avoid this index option in OLTP but not in DSS.

________________________________
From: FRANK ROTOLO <frank.pr@msn.com>
To: classics@iiug.org
Sent: Mon, March 8, 2010 12:24:40 PM
Subject: SERIAL to INTEGER key joins vs. CHAR to CHAR. [3515]

OK, so I have a "customers" table with:

customers.cust_id SERIAL(0),
customers.cust_name CHAR(30),
...
...

and a "loans" table with:

loans.cust_id INTEGER,
...
...

the tables are joined by their cust_id columns.
we are told this is the normally correct way to join master/detail tables.
it allows us to make changes in the customers name without loosing the join.

To lookup a customer's loans, my users query the master table using wildcard
names, example: LAS*SUR*F* to locate customer LASTNAME SURNAME, FIRSTNAME
Then they make the loans table active to make payment updates.
So, I created the following index:

CREATE UNIQUE CLUSTER INDEX custname ON customers(cust_name);

so that any new customers are physically ordered by name in customers.dat
great, but I'm not able to order the loans detail table the same way!
the best I could do for it is:

CREATE CLUSTER INDEX loansclidx ON loans(cust_id);

so that each customers loans are physically grouped together in loans.dat
Now, everytime a new customer is added, the next available serial cust_id
is assigned to the customer, regardless of the customers name. As a result,
the rows in the loans table are not in the same order as the customer table,
and fetching each customers loans takes longer because although the loans rows
are grouped together in ascending order by loans.cust_id, they are not ordered
the same way as the customers name in the master.

I experimented by doing away with customers.cust_id = loans.cust_id, making
customers.cust_name the PRIMARY KEY which now joins loans.cust_name (an added
column). Although this is not the "correct" way to define joins, because if
you make a change in the master tables customer name the join is severed in
the detail table, unless you update each detail row with the new change.
But now we have:

CREATE UNIQUE CLUSTER INDEX custname ON customers(cust_name);
CREATE CLUSTER INDEX loanname ON loans (cust_name);

and response time is incredible!

Is there a better way of doing this?

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• SERIAL to INTEGER key joins vs. CHAR to CHAR.
  FRANK ROTOLO -- Monday, 8 March 2010, at 12:24 a.m.
  • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
    John Miller iii -- Monday, 8 March 2010, at 1:55 a.m.
  • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
    Art Kagel -- Monday, 8 March 2010, at 6:55 a.m.
    • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
      FRANK ROTOLO -- Monday, 8 March 2010, at 2:57 p.m.
      • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
        Art Kagel -- Monday, 8 March 2010, at 6:51 p.m.
        • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
          FRANK ROTOLO -- Monday, 8 March 2010, at 8:45 p.m.
    • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
      FRANK ROTOLO -- Monday, 8 March 2010, at 3:35 p.m.
      • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
        Jonathan Leffler -- Wednesday, 10 March 2010, at 2:04 a.m.
  • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
    Pankrich Khanthasa-ard -- Monday, 8 March 2010, at 11:01 a.m.
    • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
      Art Kagel -- Monday, 8 March 2010, at 11:46 a.m.
      • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
        FRANK ROTOLO -- Monday, 8 March 2010, at 3:08 p.m.
        • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
          Art Kagel -- Monday, 8 March 2010, at 7:25 p.m.
          • Re: SERIAL to INTEGER key joins vs. CHAR to CHAR.
            FRANK ROTOLO -- Monday, 8 March 2010, at 9:20 p.m.
          • RE: SERIAL to INTEGER key joins vs. CHAR to CHAR.
            Ian Michael Gumby -- Tuesday, 9 March 2010, at 12:31 p.m.
            • Re: RE: SERIAL to INTEGER key joins vs. CHAR to CH
              FRANK ROTOLO -- Tuesday, 9 March 2010, at 6:18 p.m.
    • RE: SERIAL to INTEGER key joins vs. CHAR to CHAR.
      Zev Berezin -- Tuesday, 9 March 2010, at 3:18 p.m.

Classics Forum is maintained by Administrator with WebBBS 5.12.